package com.homework.dao;

import com.homework.bin.user;
import com.homework.util.DBUtil1;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author： Administrator
 * @date： 2021-05-14 14:54
 * @version 1.0
 */
public class userdao {



    public static int insert(user us) throws ClassNotFoundException, SQLException {

// 1，获取连接.
        Connection con=DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql = "insert into J_USER values(SEQ_J_USER.nextval,?,?,1)";
        //2，2创建prepareStatement对象，执行 sqL语句
        PreparedStatement ps = con.prepareStatement(sql);
        //3.给占位符进行赋值
        ps.setString(1,us.getUsername());
        ps.setString(2,us.getPassword());
        int i = ps.executeUpdate();
//3.资源释放
        DBUtil1.close();
        return i;
    }
    public static ResultSet selectbyusernameanpassword(user us) throws ClassNotFoundException, SQLException {

        //1，获取连接.
        int i=0;
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from J_USER where(USERNAME=? and PASSWORD=?)";
        PreparedStatement ps = con.prepareStatement(sql1);
        ps.setString(1,us.getUsername());
        ps.setString(2,us.getPassword());
        ResultSet rs=ps.executeQuery();
        // 4，处理查询的结果集
       /* while(rs.next()){
            // 获取当前行中的数据
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            int state = rs.getInt("state");
            us = new user(id,username,password,state);
        }*/
        // 5，释放资源，还回连接池中
        DBUtil1.close();

        return rs;
    }
    public static ResultSet selectbyusername(user us) throws ClassNotFoundException, SQLException {

        //1，获取连接.
        int i=0;
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from J_USER where USERNAME=?";
        PreparedStatement ps = con.prepareStatement(sql1);
        ps.setString(1,us.getUsername());
        ResultSet rs=ps.executeQuery();
        // 4，处理查询的结果集
        // 5，释放资源，还回连接池中
        DBUtil1.close();

        return rs;
    }
    //查询
    public static List<user> result() throws ClassNotFoundException, SQLException {
        //0.创建集合
        List<user> user=new ArrayList<>();
        //1，获取连接.
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select id,username,password,state from J_USER";
        PreparedStatement ps = con.prepareStatement(sql1);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            int id=rs.getInt("id");
            String username=rs.getString("username");
            String password=rs.getString("password");
            int state=rs.getInt("state");
            //控制台输出
          /*  System.out.println("id："+id+"，username："+username+
                    "，password："+password+"，state："+state);*/
            user user1=new user(id,username,password,state);
            user.add(user1);
        }
        DBUtil1.close();
        return user;
    }
    //删除
    public static int delete(user us)throws ClassNotFoundException, SQLException{

        //1，获取连接.
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql2= "delete from J_USER where USERNAME=? and PASSWORD=?";
        PreparedStatement ps = con.prepareStatement(sql2);
        ps.setString(1,us.getUsername());
        ps.setString(2,us.getPassword());
        int i = ps.executeUpdate();
        DBUtil1.close();
        return i;
    }
    public static int update(user us)throws ClassNotFoundException, SQLException{

        //1，获取连接.
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql3="update J_USER set PASSWORD=? where USERNAME=?";
        PreparedStatement  ps = con.prepareStatement(sql3);
        ps.setString(1,us.getPassword());
        ps.setString(2,us.getUsername());
        int i = ps.executeUpdate();
        DBUtil1.close();
        return i;

    }
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        user user=new user();
        user.setUsername("ljm_test1");
        user.setPassword("20220420");
        userdao.insert(user);
    }
}

